from openpyxl import load_workbook,Workbook
import random  

surnames = ['赵', '钱', '孙', '李', '周', '吴', '郑', '王']  
names = ['伟', '明', '东', '强', '燕', '婷', '静', '玲']  
  
def generate_student_data(num_students):  
    students = []  
    for _ in range(num_students):  
        student_name = random.choice(surnames) + random.choice(names)  
        scores = {}  
        for subject in ['语文', '数学', '英语']:  
            scores[subject] = [random.randint(60, 100) for _ in range(3)]  
        students.append({'name': student_name, 'scores': scores})  
    return students  
  
# 写入Excel文件的函数  
def write_to_excel(students, filename):  
    wb = Workbook()  
    ws = wb.active  
    ws.append(['姓名', '课程', '成绩'])  
    for student in students:  
        for subject, scores in student['scores'].items():  
            for score in scores:  
                ws.append([student['name'], subject, score])  
    wb.save(filename)  
  
# 从Excel文件中读取数据并处理  
def process_excel_data(filename_input, filename_output):  
    wb_input = load_workbook(filename_input)  
    ws_input = wb_input.active  
      
    # 构建一个字典来存储每个学生的平均成绩  
    students_avg_scores = {}  
      
    # 遍历Excel表格中的数据  
    for row in ws_input.iter_rows(min_row=2, values_only=True):  # 从第二行开始，因为第一行是标题  
        name, subject, score = row  
        if name not in students_avg_scores:  
            students_avg_scores[name] = {'语文': 0, '数学': 0, '英语': 0, 'total': 0}  
        students_avg_scores[name][subject] += score  
      
    # 计算每个学生的平均成绩和总分  
    for name, scores in students_avg_scores.items():  
        for subject in ['语文', '数学', '英语']:  
            scores[subject] /= 3  
            scores['total'] += scores[subject]  
      
    # 对学生的总分进行排序，并添加排名  
    sorted_students = sorted(students_avg_scores.items(), key=lambda x: x[1]['total'], reverse=True)  
    ranks = {name: i+1 for i, (name, _) in enumerate(sorted_students)}  
      
    # 写入新的Excel文件  
    wb_output = Workbook()  
    ws_output = wb_output.active  
    ws_output.append(['姓名', '语文', '数学', '英语', '总分', '排名'])  
    for name, scores in sorted_students:  
        ws_output.append([name, scores['语文'], scores['数学'], scores['英语'], scores['total'], ranks[name]])  
    wb_output.save(filename_output)  
  
# 主程序  
if __name__ == '__main__':  

    students = generate_student_data(10)  
    write_to_excel(students, 'scores_raw.xlsx')  
    process_excel_data('scores_raw.xlsx', 'scores_processed.xlsx')